﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Windows.Forms;
using System.Data;
using System.Data.Common;
using StockSBDetails;

namespace StockSBIE
{
    public class StockDataManager
    {
        HashSet<String> registeredCodes = new HashSet<String>();

        public static DateTime getMaxTradeDate()
        {
            String sql = "select max([日期])  from StockDaily";

            DbCommand cmd = SQLFactory.getInstance().getCommand();
            cmd.CommandText = sql;

            DbDataReader rdr = cmd.ExecuteReader();
            if (rdr.Read())
            {
                String dateStr = rdr.GetValue(0).ToString();
                rdr.Close();
                cmd.Dispose();
                if (String.IsNullOrWhiteSpace(dateStr) == false)
                {
                    DateTime d = DateTime.Now;
                    if (DateTime.TryParse(dateStr, out d))
                    {
                        return d;
                    }
                }
            }
            rdr.Close();
            cmd.Dispose();
            return DateUtils.getValidTradeDate(DateTime.Now);
        }

        HashSet<String> storedData = new HashSet<string>();
        protected bool hasStoredData(String code)
        {
            return storedData.Contains(code);
        }
        public void preLoadCode()
        {
            registeredCodes.Clear();
            //目前已經有的code
            DbCommand cmd = SQLFactory.getInstance().getCommand();
            cmd.CommandText = "select stockCode from StockCodes";
            DbDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                registeredCodes.Add(rdr.GetString(0));
            }
            rdr.Close();
            cmd.Dispose();
        }
        public void preloadStockDaily(DateTime d)
        {
            storedData.Clear();
            String sql = "select [證券代號] from StockDaily where 日期 = @date";
            DbCommand cmd = SQLFactory.getInstance().getCommand();
            cmd.CommandText = sql;
            cmd.Parameters.Add(SQLFactory.getInstance().createParam("@date", DateUtils.getSlashYYYYMMDD(d)));
            DbDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                String code = rdr.GetString(0);
                storedData.Add(code);
            }
            rdr.Close();
            cmd.Dispose();
        }

        protected bool hasCode(String code)
        {
            if (registeredCodes.Contains(code))
                return true;
            return false;
        }
        protected void insertNewCode(DateTime d, String code, String name, String market)
        {
            ArrayList ps = new ArrayList();

            List<String> names = new List<string>();
            names.Add("@id");
            names.Add("@stockCode");
            names.Add("@stockName");
            names.Add("@createdAt");
            names.Add("@market");
            names.Add("@caregory");
            names.Add("@codeLen");


            ps.Add(NewGUID.create("stck").ToString("B"));
            ps.Add(code);
            ps.Add(name);
            ps.Add(DateUtils.getSlashYYYYMMDD(d));
            ps.Add(market);
            ps.Add(null);
            ps.Add(code.Length);
            String sql = "Insert into StockCodes (id, stockCode, stockName, createdAt, market, caregory, codeLen)  values (@id, @stockCode, @stockName, @createdAt, @market, @caregory, @codeLen)";
            try
            {
                SQLFactory.getInstance().executeInsert(sql, names, ps);
                registeredCodes.Add(code);
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

        public void updateStockDaily(DateTime d, List<List<String>> data, String market)
        {
            String sqldel = "delete from StockDaily where [日期]= @date and  [證券代號] in (select stockCode from stockCodes where market='tse')";
            DbCommand cmd = SQLFactory.getInstance().getCommand();
            cmd.CommandText = sqldel;
            cmd.Parameters.Add(SQLFactory.getInstance().createParam("@date", DateUtils.getSlashYYYYMMDD(d)));


            String sql = "insert into StockDaily ([日期],[證券代號],[成交股數],[成交筆數],[成交金額]," +
                                                              "[開盤價], [最高價],[最低價], [收盤價], [漲跌], [漲跌價差]," +
                                                              "[最後揭示買價],[最後揭示買量], [最後揭示賣價], [最後揭示賣量], [本益比]) " +
                                                              "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            DbTransaction transaction = SQLFactory.getInstance().BeginTransation();


            cmd.CommandText = sql;
            foreach (List<String> row in data)
            {
                if (row[0].Contains("證券"))
                    continue;


            }
            transaction.Commit();
            cmd.Dispose();

        }
        public void updateStockDaily(DateTime d, List<String> data, String market)
        {

            decimal v = new decimal(0);
            ArrayList ps = new ArrayList();

            String ds = DateUtils.getSlashYYYYMMDD(d);
            ps.Add(ds);

            //證券代號[0]
            String 證券代號 = data[0];
            ps.Add(證券代號);


            //證券名稱[1] ,不需要insert
            String 證券名稱 = data[1];

            if (hasCode(證券代號) == false)
            {
                insertNewCode(d, 證券代號, 證券名稱, market);
            }

            if (hasStoredData(證券代號))
                return;

            //成交股數[2]
            if (decimal.TryParse(data[2], out v))
            {
                ps.Add(Convert.ToInt32(v));
            }
            else
            {
                ps.Add(0);
            }
            //成交筆數[3]
            if (decimal.TryParse(data[3], out v))
            {
                ps.Add(Convert.ToInt32(v));
            }
            else
            {
                ps.Add(0);
            }
            //成交金額[4]
            if (decimal.TryParse(data[4], out v))
            {
                ps.Add(v);
            }
            else
            {
                ps.Add(0);
            }
            //開盤價[5]
            if (decimal.TryParse(data[5], out v))
            {
                ps.Add(v);
            }
            else
            {
                ps.Add(null);
            }
            //最高價[6]
            if (decimal.TryParse(data[6], out v))
            {
                ps.Add(v);
            }
            else
            {
                ps.Add(null);
            }
            //最低價[7]
            if (decimal.TryParse(data[7], out v))
            {
                ps.Add(v);
            }
            else
            {
                ps.Add(null);
            }
            //收盤價[8]
            if (decimal.TryParse(data[8], out v))
            {
                ps.Add(v);
            }
            else
            {
                ps.Add(null);
            }
            //漲跌[9]
            bool positive = true;
            {
                if (data[9].Contains("＋"))
                    ps.Add("漲");
                else
                    if (data[9].Contains("－"))
                    {
                        ps.Add("跌");
                        positive = false;
                    }
                    else
                        if (data[9].Contains("X"))
                            ps.Add("不比價");
                        else
                            ps.Add("");
            }
            //漲跌價差[10]
            if (decimal.TryParse(data[10], out v))
            {
                if (positive == false)
                    v = (new decimal(0)) - v;
                ps.Add(v);
            }
            else
            {
                ps.Add(null);
            }
            //最後揭示買價[11]
            if (decimal.TryParse(data[11], out v))
            {
                ps.Add(v);
            }
            else
            {
                ps.Add(null);
            }
            //最後揭示買量[12]
            if (decimal.TryParse(data[12], out v))
            {
                ps.Add(Convert.ToInt32(v));
            }
            else
            {
                ps.Add(null);
            }

            //最後揭示賣價[13]
            if (decimal.TryParse(data[13], out v))
            {
                ps.Add(v);
            }
            else
            {
                ps.Add(null);
            }
            //最後揭示賣量[14]
            if (decimal.TryParse(data[14], out v))
            {
                ps.Add(Convert.ToInt32(v));
            }
            else
            {
                ps.Add(null);
            }
            //本益比[15]
            if (decimal.TryParse(data[15], out v))
            {
                ps.Add(v);
            }
            else
            {
                ps.Add(null);
            }
            List<String> names = new List<string>();

            names.Add("@日期"); names.Add("@證券代號"); names.Add("@成交股數"); names.Add("@成交筆數"); names.Add("@成交金額");
            names.Add("@開盤價"); names.Add("@最高價"); names.Add("@最低價"); names.Add("@收盤價"); names.Add("@漲跌"); names.Add("@漲跌價差");
            names.Add("@最後揭示買價"); names.Add("@最後揭示買量"); names.Add("@最後揭示賣價"); names.Add("@最後揭示賣量"); names.Add("@本益比");


            String sql = "insert into StockDaily ([日期],[證券代號],[成交股數],[成交筆數],[成交金額]," +
                                                                          "[開盤價], [最高價],[最低價], [收盤價], [漲跌], [漲跌價差]," +
                                                                          "[最後揭示買價],[最後揭示買量], [最後揭示賣價], [最後揭示賣量], [本益比]) " +
                                                                          "values (@日期,@證券代號,@成交股數,@成交筆數,@成交金額, " +
                                                                          " @開盤價, @最高價,@最低價, @收盤價, @漲跌, @漲跌價差, " +
                                                                          "@最後揭示買價,@最後揭示買量, @最後揭示賣價, @最後揭示賣量, @本益比)";
            SQLFactory.getInstance().executeInsert(sql, names, ps);


            storedData.Add(證券代號);
        }

    }
}
